package com.eatrip.dao.implementation;

import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.eatrip.bean.DatiLocale;
import com.eatrip.dao.DatiLocaleDAO;

@Repository
@Transactional
public class DatiLocaleDAOImple implements DatiLocaleDAO {
	
	private HibernateTemplate hibernateTemplate;
	
	@Autowired
	public void setSessionFactory(SessionFactory sessionFactory){
		hibernateTemplate= new HibernateTemplate(sessionFactory);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public DatiLocale getDatiLocale(int idLocale) {
		List<DatiLocale> list = hibernateTemplate.find("from DatiLocale where idLocale=?", idLocale);
		if (list.size() == 0)
			return null;
		return list.get(0);
	}

	
	@SuppressWarnings("unchecked")
	@Override
	public List<DatiLocale> getDatiLocaleBestValutazione(int numRow) {
		List<DatiLocale> list = hibernateTemplate.getSessionFactory().getCurrentSession().createSQLQuery("SELECT * FROM dati_locale d INNER JOIN valutazioni v on d.id_locale = v.id_locale_fk ORDER BY v.valore")
				.addEntity("d",DatiLocale.class)
				//.addEntity("v",Valutazione.class)
				.setMaxResults(numRow)
				.list();
		if (list.size() == 0)
			return null;
		return list;
	}

	
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DatiLocale> getDatiEDettagliLocaleByNomeComune(String nome,int start,int numRow) {
		
		int offset = numRow * (start - 1);

		Query q = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery("select da,de from DatiLocale as da join fetch da.dettagliLocale de where da.comune in ( select co.id from Comune co where co.nome like :nome)");
		q.setParameter("nome", nome);
		q.setFirstResult(offset);
		q.setMaxResults(numRow*3);
		
		List<DatiLocale> list = q.list();
				
		if (list.size() == 0)	
			return null;
		return list;
	}
	
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DatiLocale> getDatiEDettagliLocaleByRegione(String nome,
			int start, int numRow) {
		
		int offset = numRow * (start - 1);
		
		Query q = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery("select da from DatiLocale da join fetch da.comune co join fetch co.provincia pr join fetch pr.regione re where re.nome like :nome");
		//Query q = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery("select fo from FotoLocale fo join fetch fo.datiLocale da join fetch da.provincia pr join fetch pr.regione re where re.nome like :nome");
		q.setParameter("nome", nome);
		q.setFirstResult(offset);
		q.setMaxResults(numRow*3);
		
		List<DatiLocale> list = q.list();
				
		if (list.size() == 0)	
			return null;
		return list;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DatiLocale> getDatiEDettagliLocaleByRegione(String nome,int start, int numRow, String query) {
		
		int offset = numRow * (start - 1);
		
		Query q = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery("select distinct da\n" +
																						"from DatiLocale da join fetch da.dettagliLocale de\n" +
																						", Regione re\n" +
																						"where "+query+"\n" +
																						"da.comune in ( select co.id " +
																						"				from Comune co,\n" +
																						"					 Provincia pr,\n" +
																						"					 Regione re" +
																						"				where co.provincia = pr.id\n" +
																						"				      and pr.regione = re.id\n" +
																						"					  and re.nome like :nome )");
		q.setParameter("nome", nome);
		q.setFirstResult(offset);
		q.setMaxResults(numRow*3);
		
		List<DatiLocale> list = q.list();
				
		if (list.size() == 0)	
			return null;
		return list;
	}

	
	
	

	@SuppressWarnings("unchecked")
	@Override
	public List<DatiLocale> getDatiEDettagliLocaleByComuneEDettagli(
			String nome, int start, int numRow, String query) {
		
		int offset = numRow * (start - 1);
		
		Query q = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery("select da,de from DatiLocale as da join fetch da.dettagliLocale de where ("+query+"  da.comune in ( select co.id from Comune co where co.nome like '%"+nome+"%'))");
		q.setFirstResult(offset);
		q.setMaxResults(numRow*3);
		
		
		List<DatiLocale> list = q.list();
				
		if (list.size() == 0)	
			return null;
		return list;
	}

	@Override
	public void deleteDatiLocale(DatiLocale datiLocale) {
		hibernateTemplate.delete(datiLocale);
	}

	@Override
	public void insertDatiLocale(DatiLocale datiLocale) {
		hibernateTemplate.saveOrUpdate(datiLocale);
	}

	@Override
	public void updateDatiLocale(DatiLocale datiLocale) {
		hibernateTemplate.update(datiLocale);
	}

	@Override
	public void deleteAllDatiLocale() {
		Query q = hibernateTemplate.getSessionFactory().getCurrentSession().createQuery("DELETE FROM DatiLocale");
		int row = q.executeUpdate();
		if (row == 0)
			System.out.println("errore nella cancellazione dei dati locale");
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<DatiLocale> getAllDatiLocale() {
		return hibernateTemplate.find("FROM DatiLocale");
	}

	@SuppressWarnings("unchecked")
	@Override
	public DatiLocale getDatiEDettagliLocaleByEmail(String email) {
		Criteria criteria = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(DatiLocale.class)
				.add(Restrictions.eq("email", email));
		
		List<DatiLocale> list = criteria.list();
		
		if (list.size() == 0)
			return null;
		
		return list.get(0);
	}

}
